package pandora.data.mysql;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import pandora.connection.ConnectionManager;
import pandora.data.ICustomerDAO;
import pandora.data.IRatingDAO;
import pandora.data.IStoreDAO;
import pandora.dto.CustomerDTO;
import pandora.dto.RatingDTO;
import pandora.dto.StoreDTO;
import pandora.utils.UIDGenerator;

public class JDBCRatingDAO implements IRatingDAO {
	
	private Connection conn;
		
	public JDBCRatingDAO(){
		conn = ConnectionManager.getInstance().checkOut();
	}
	
    protected void finalize() {
        ConnectionManager.getInstance().checkIn(conn);
    }

	public Float getAverageRating(String nif) {
		float numP = 0;
		float total = 0;
		
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			stmt = conn.createStatement();
			
			IStoreDAO storeDAO = new JDBCStoreDAO();
			
			
			String sql = "select value from Ratings where storeOID='" + storeDAO.selectStoreOIDByNIF(nif) + "'";
			rs = stmt.executeQuery(sql);
						
			while (rs.next()){
				numP++;
				
				total += rs.getInt("value");	
			}			
		
		 } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	     } finally {
	            try {
	                if (rs != null)
	                    rs.close();
	                if (stmt != null)
	                    stmt.close();
	            } catch (SQLException e) {
	            	System.out.println("RESULTSET CLOSING ERROR OR STATEMENT CLOSING ERROR");
	            }
	     }
		
		Float media;
		media=total/numP;
		
		return media;
	}

	public void saveRatings(List<RatingDTO> puntuaciones) {
		Statement stmt = null;
		
		IStoreDAO storeDAO = new JDBCStoreDAO();
		ICustomerDAO customerDAO = new JDBCCustomerDAO();
		
		try {
			stmt = conn.createStatement();
			IRatingDAO ratingDTO = new JDBCRatingDAO();
			
			String ratingOID = null;		
			for (RatingDTO r: puntuaciones) {
				ratingOID = ratingDTO.selectRatingOID(r.getStore(),r.getCustomer(),r.getRateType());
				
				if (ratingOID !=null) {
					String sql = "update Ratings set value=";
					sql = sql + r.getRateValue();
					sql = sql + " where OID='"+ratingOID+"'";
					stmt.executeUpdate(sql);
				} else {
					String sql = "insert into Ratings(OID,storeOID,customerOID,value,type) values ";
					sql = sql + "('";				
					sql = sql + UIDGenerator.getInstance().getKey() + "','";
					sql = sql + storeDAO.selectStoreOIDByNIF(r.getStore().getStoreNIF()) + "','";
					sql = sql + customerDAO.selectCustomerOIDByDNI(r.getCustomer().getCustomerDNI()) + "',";
					sql = sql + r.getRateValue() + ",'";
					sql = sql + r.getRateType();
					sql = sql + "')";
				
					stmt.executeUpdate(sql);
				}
			}			
			
		} catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
            try {
            	if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            	System.out.println("RESULTSET CLOSING ERROR OR STATEMENT CLOSING ERROR");
            }
		}
				
	}
	
	public Float getAverageRatingByType(StoreDTO store, String type) {
		float numP = 0;
		float total = 0;
		
		Statement stmt = null;
		ResultSet rs = null;
				
		IStoreDAO storeDAO = new JDBCStoreDAO();
		
		try {
			stmt = conn.createStatement();
			
			String sql = "select value from Ratings where storeOID='" + storeDAO.selectStoreOIDByNIF(store.getStoreNIF()) + "' and type='" + type + "'";
			
			rs = stmt.executeQuery(sql);
						
			while (rs.next()){
				numP++;
				
				total += rs.getInt("value");				
				
			}			
		
		} catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
            try {
                if (rs != null)
                    rs.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            	System.out.println("RESULTSET CLOSING ERROR OR STATEMENT CLOSING ERROR");
            }
     }
		
		Float media;
		media=total/numP;
		
		return media;
	}
	
	public RatingDTO getRatingByUser(String nif,String type, String usr) {
		
		Statement stmt = null;
		ResultSet rs = null;
		RatingDTO puntuacion = null;
		
		try {
			IStoreDAO storeDAO = new JDBCStoreDAO();
			ICustomerDAO customerDAO = new JDBCCustomerDAO();
			
			CustomerDTO customer = customerDAO.selectCustomerByAccount(usr);
			String customerOID = customerDAO.selectCustomerOIDByDNI(customer.getCustomerDNI());
			
			String storeOID = storeDAO.selectStoreOIDByNIF(nif);
			
			stmt = conn.createStatement();
			
			String sql = "select * from Ratings where storeOID = '" + storeOID + "' and customerOID = '" + customerOID + "' and type = '" + type+  "'";
						
			rs = stmt.executeQuery(sql);
						
			
			if (rs.next()){
				puntuacion = new RatingDTO();
				
				puntuacion.setStore(storeDAO.getStoreByNIF(nif));
				puntuacion.setCustomer(customer);
								
				puntuacion.setRateType(rs.getString("type"));
				puntuacion.setRateValue(rs.getInt("value"));
							
			}
			
		} catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
            try {
                if (rs != null)
                    rs.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            	System.out.println("RESULTSET CLOSING ERROR OR STATEMENT CLOSING ERROR");
            }
     }
		
		return puntuacion;
	}

	@Override
	public String selectRatingOID(StoreDTO store, CustomerDTO customer,
			String type) {
		
		PreparedStatement stmt = null;
		ResultSet result = null;
		String oid = null;
		String sql = "SELECT * FROM Ratings r WHERE (r.storeOID = ?) and (r.customerOID = ?) and (r.type = ?)";

		try {
			IStoreDAO storeDAO= new JDBCStoreDAO();
			ICustomerDAO customerDAO = new JDBCCustomerDAO();
			
			stmt = conn.prepareStatement(sql);

			stmt.setString(1, storeDAO.selectStoreOIDByNIF(store.getStoreNIF()));
			stmt.setString(2, customerDAO.selectCustomerOIDByDNI(customer.getCustomerDNI()));
			stmt.setString(3, type);
			
			result = stmt.executeQuery();
			
			if (result.next()) {
	                oid = result.getString("OID");
			}
		
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		
		} finally {
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
	                    stmt.close();
	            }
			} catch (SQLException e) {
	            }
		}
	    
		return oid;
	}

}
